Open Database Connectivity (ODBC) is an open standard application programming interface (API) for accessing a database. By using ODBC statements in a program, you can access files in a number of different databases, including Access, dBase, DB2, Excel, and Text. In addition to the ODBC software, a separate module or driver is needed for each database to be accessed. The main proponent and supplier of ODBC programming support is Microsoft. ODBC is based on and closely aligned with The Open Group standard Structured Query Language (SQL) Call-Level Interface. It allows programs to use SQL requests that will access databases without having to know the proprietary interfaces to the databases. ODBC handles the SQL request and converts it into a request the individual database system understands.
Before an ODBC application can submit an SQL statement, it must allocate a statement handle for the statement. To allocate a statement handle, an application declares a variable of type HSTMT (for example, the application could use the declaration: HSTMT hstmt1) and then calls SQLAllocStmt to pass it the address of the variable and the connected HDBC with which to associate the statement. The driver allocates memory to store information about the statement, associates the statement handle with the hdbc, and returns the statement handle in the variable.
OLEDB is an application program interface (API) for access to different data sources. OLEDB includes not only the Structured Query Language (SQL) capabilities of the Microsoft-sponsored standard data interface Open Database Connectivity (ODBC) but also includes access to data other than SQL data. As a design from Microsoft's Component Object Model (COM), OLEDB is a set of methods (or routines) for reading and writing data. The objects in OLEDB consist mainly of a data source object, a session object, a command object, and a rowset object. An application using OLEDB would use this request sequence: (i) initialize OLE; (ii) connect to a data source; (iii) issue a command; (iv) process the results; and (v) release the data source object and uninitialize OLE. (While OLE once stood for “Object Link Embedding” and “DB” for database, many skilled artisans no longer ascribes these meanings to the letters “OLE” and “DB.”)
In OLEDB, ICommand contains methods to execute commands. A command can be executed many times, and the parameter values can vary. This interface is mandatory on commands. A command object contains a single text command, which is specified through ICommandText, an interface that is mandatory on commands, and a command object can have only one text command. When the command text is specified through ICommandText::SetCommandText, it replaces the existing command text.
ActiveX Data Object (ADO) is an application program interface from Microsoft that lets a programmer writing Windows applications get access to a relational or non-relational database from both Microsoft and other database providers. For example, to write a program that would provide users of a Web site with data from an IBM DB2 database or an Oracle database, one could include ADO program statements in an HTML file that are then identified as an Active Server Page; then, when a user requested the page from the Web site, the page sent back would include appropriate data from a database, obtained using ADO code.
OLEDB is the underlying system service that a programmer using ADO is actually using. A feature of ADO, Remote Data Service, supports “data-aware” ActiveX controls in Web pages and efficient client-side caches. As part of ActiveX, ADO is also part of Microsoft's overall Component Object Model (COM), its component-oriented framework for putting programs together. ADO instruction objects are termed commands as the usual intention is for these commands to be performed immediately. The most elemental responsibility of a command object is to perform the operations intended by an instruction. Additional responsibilities might include: undoing operations; performing the operations in a different context, possibly in a different process, machine, or time schedule (batching); pr simulating the effect of the operations. For programming languages that do not provide threading, running batched CommandObjects can provide pseudo-threading.
ADO.net is a set of classes in .NET that allows for data access. ADO.net is comprised of classes found in the System.Data namespace that encapsulate data access for distributed applications. However, rather than simply mapping the existing ADO object model to .NET to provide a managed interface to OLEDB and SQL Server, ADO.net changes the way data is stored and marshaled within and between applications. The primary reason ADO.net redefines this architecture is that most applications developed today can benefit from the scalability and flexibility of being able to distribute data across the Internet in a disconnected fashion.
Because the classic ADO model was developed primarily with continuously connected access in mind, creating distributed applications with it is somewhat limiting. A typical example is the need to move data through a Recordset object between tiers in a distributed application. To accomplish this in classic ADO you have to specifically create a disconnected Recordset using a combination of properties including cursor location, cursor type, and lock type. In addition, because the Recordset is represented in a proprietary binary format, you have to rely on COM marshalling code built into OLEDB to allow the Recordset to be passed by value (ByVal) to another component or client code. This architecture also runs into problems when attempting to pass recordsets through firewalls because these system level requests are often denied. On the other hand, if you elected not to use disconnected recordsets, you had to devise your own scheme to represent the data using Variant arrays, delimited within a string, or saved as tabular XML using the Save method (although the latter option is really only viable when using ADO 2.5 and higher). Obviously these approaches have their downside because they run into problems with performance and maintainability not to mention interoperability between platforms. In addition, the classic ADO model doesn't handle hierarchical data particularly well. Although it is possible to create hierarchical recordsets using the Microsoft data shape provider, it is not simple and is therefore not often used. Typically JOIN clauses are used inside stored procedures or inline SQL to retrieve data from multiple tables. However, this does not allow you to assemble data from multiple data sources and easily determine from where the data comes. As a result, classic ADO provides a flat view of data that is not strongly typed. To alleviate these problems, ADO.net is built from the ground up for distributed applications used in today's disconnected scenarios. For example, the central class in ADO.net is the DataSet, which can be thought of as an in-memory XML database that stores related tables, relationships, and constraints. As you'll see, the DataSet is the primary mechanism used in VB.NET applications to cache data and pass it between tiers in a distributed application thereby alleviating the need to rely on proprietary schemes or COM marshalling.
Using XML alleviates several of the burdens of classic ADO. For example, by storing the data as XML it can easily pass through firewalls without special configuration. In addition, by storing related tables and representing the relationships between those tables the DataSet can store data hierarchically allowing for the easy manipulation of parent/child relationships. The self-describing nature of XML combined with the object-oriented nature of VB.NET also allows for direct programmatic access to the data in a DataSet in a strongly typed fashion. In other words, the data need not be accessed using a tables, rows, and columns metaphor but can be accessed in terms of the definition of the data that can be type checked by the compiler. Furthermore, this disconnected model combined with connection pooling schemes frees resources on the database server more quickly, allowing applications to scale by not holding on to expensive database connections and locks.
Web services (sometimes called application services) are services that are made available from a business's Web server for Web users or other Web-connected programs. Providers of Web services are generally known as application service providers. Web services range from such major services as storage management and customer relationship management (CRM) down to much more limited services such as the furnishing of a stock quote and the checking of bids for an auction item. The accelerating creation and availability of these services is a major Web trend. Users can access some Web services through a peer-to-peer arrangement rather than by going to a central server. Some services can communicate with other services and this exchange of procedures and data is generally enabled by a class of software known as middleware. Services previously possible only with the older standardized service known as Electronic Data Interchange (EDI) increasingly are likely to become Web services. Besides the standardization and wide availability to users and businesses of the Internet itself, Web services are also increasingly enabled by the use of the Extensible Markup Language (XML) as a means of standardizing data formats and exchanging data. XML is the foundation for the Web Services Description Language (WSDL).
.NET is a collection of programming support for web services, the ability to use remote services rather than your own computer for various services. The purpose of .NET is to provide individual and business users with a seamlessly interoperable interface for applications and computing devices and to make computing activities increasingly browser-oriented. The .NET platform includes servers; building-block services, such as web-based data storage; and device software.
The .NET Common Language Runtime (CLR) is an execution platform that manages the execution of intermediate language (IL) code generated from any one of several programming languages, and the CLR allows these different IL code components to share common object-oriented classes written in any of the supported languages—for example, the CLR allows an instance of a class written in one language to call a method of a class written in another language. Moreover, programs compiled for the CLR do not need a language-specific execution environment, and these programs can easily be moved and executed on any system with CLR support.
Programmers developing code in Visual Basic, Visual C++, or C# compile their programs into intermediate language code called Common Intermediate Language (CIL) in a portable execution (PE) file that can then be managed and executed by the CLR. The programmer and the environment specify descriptive information about the program when it is compiled and the information is stored with the compiled program as metadata. This metadata, stored in the CIL compiled program, tells the CLR what language was used, its version, and what class libraries will be needed by the program for execution.
The CLR also provides services, such as automatic memory management, garbage collecting (returning unneeded memory to the computer), exception handling, and debugging, and thus provides a powerful yet easy-to-use programming model. The CLR is sometimes referred to as a “managed execution environment” (MEE), and the IL code that executes within the CLR is called managed code. Currently a CLR running on a server that also hosts a DBMS is problematic because the CLR can compromise the reliability, scalability, security, and robustness of the DBMS. For example, when operating independently on the same server, both the DBMS and CLR manage memory, threads, and synchronization between multiple threads, and sometimes conflicts can result. Various embodiments of the present invention provides a solution to this problem.
In computer programming, a connection is the setting up of resources (such as computer memory and buffers) so that a particular object such as a database or file can be read or written to. Typically, a programmer encodes an OPEN or similar request to the operating system that ensures that system resources such as memory are set up, encodes READs and WRITES or similar requests, and then encodes a CLOSE when a connection is no longer needed so that the resources are returned to the system for other users.
Previous releases of SQL Server provided extensibility through extended stored procedures. To access data from the local instance developers had to use a data access API (ODBC/OLEDB) to loop back to the server and perform data access being agnostic to the condition of running inproc as part of an already established connection.
In a database, procedures and functions performs a distinct service. The language statement that requests the function is called a function call. Programming languages usually come with a compiler and a set of “canned” functions that a programmer can specify by writing language statements. These provided functions are sometimes referred to as library routines. Some functions are self-sufficient and can return results to the requesting program without help. Other functions need to make requests of the operating system in order to perform their work.
In a database, a trigger is a set of Structured Query Language (SQL) statements that automatically “fires off” an action when a specific operation, such as changing data in a table, occurs. A trigger consists of an event (an INSERT, DELETE, or UPDATE statement issued against an associated table) and an action (the related procedure). Triggers are used to preserve data integrity by checking on or changing data in a consistent manner.
T-SQL (Transact-SQL) is a set of programming extensions that add several features to the Structured Query Language (SQL) including transaction control, exception and error handling, row processing, and declared variables. Microsoft's SQL Server and Sybase's SQL server support T-SQL statements.
Heretofore, to run application code in a relational database management system (RDBMS), such code had to be Transact-SQL (TSQL) code. The alternative was for an application run its code in its current location and call-up the data it was processing, row by row, from the corresponding database. Neither approach is efficient, and what is needed is an alternative approach that is both efficient and effective.